﻿
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using MySchool;
using System.Text.RegularExpressions;

namespace MyKTV
{
    public partial class chaxun_gequ : Form
    {
        DBHelper db = new DBHelper();
        SqlDataAdapter adapter;
        DataSet set = new DataSet();
        public chaxun_gequ()
        {
            InitializeComponent();
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            
        }

        //初始
        private void chaxun_gequ_Load(object sender, EventArgs e)
        {
            this.dataGridView1.AutoGenerateColumns = false;

            db.OpenConnection();
            string sql = "select song_id,song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info" +
                       " inner join singer_info on song_info.singer_id = singer_info.singer_id " +
                       "inner join song_type on song_info.songtype_id = song_type.songtype_id";
            adapter = new SqlDataAdapter(sql,db.Connection);
            adapter.Fill(set, "song_info");
            this.dataGridView1.DataSource = set.Tables["song_info"];

            db.OpenConnection();
            
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            
        }

        //查询歌曲
        private void button1_Click(object sender, EventArgs e)
        {
            string sql = "";

            db.OpenConnection();


            if (this.comboBox1.Text.Trim() == "全部"&&this.textBox1.Text.Equals(string.Empty))
            {
                set.Tables["song_info"].Clear();
                sql = "select song_id,song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info" +
                      " inner join singer_info on song_info.singer_id = singer_info.singer_id " +
                      "inner join song_type on song_info.songtype_id = song_type.songtype_id";
                adapter = new SqlDataAdapter(sql, db.Connection);
                adapter.Fill(set, "song_info");
                this.dataGridView1.DataSource = set.Tables["song_info"];

                db.CloseConnection();
                return;
            }

            if (this.comboBox1.Text.Trim() == "全部")
            {
                int i,w;
                if (int.TryParse(this.textBox1.Text,out i)&&int.TryParse(this.textBox1.Text, out w))
                {
                    set.Tables["song_info"].Clear();
                    int l1 = Convert.ToInt32(this.textBox1.Text);
                    sql = "select song_id,song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info" + " inner join singer_info on song_info.singer_id = singer_info.singer_id " + "inner join song_type on song_info.songtype_id = song_type.songtype_id where song_word_count='" + l1 + "' ";
                        adapter = new SqlDataAdapter(sql, db.Connection);
                        adapter.Fill(set, "song_info");
                        this.dataGridView1.DataSource = set.Tables["song_info"];
                        db.CloseConnection();
                }
                else
                {
                set.Tables["song_info"].Clear();
                sql = "select song_id,song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info" +
                       " inner join singer_info on song_info.singer_id = singer_info.singer_id " +
                       "inner join song_type on song_info.songtype_id = song_type.songtype_id where song_name = N'"+this.textBox1.Text+ "' or songtype_name = N'"+this.textBox1.Text+ "' or singer_name = N'"+this.textBox1.Text+ "'";
                
                adapter = new SqlDataAdapter(sql, db.Connection);
                adapter.Fill(set, "song_info");
                this.dataGridView1.DataSource = set.Tables["song_info"];
                db.CloseConnection();


                }
            }

            if (!this.textBox1.Text.Equals(string.Empty))
            {
                     if (this.comboBox1.Text.Trim() == "按歌名查询")
                    {
                    set.Tables["song_info"].Clear();
                    sql = "select * from [dbo].[song_info] where  song_name like N'%" + this.textBox1.Text + "%'";
                        SqlCommand comm = new SqlCommand(sql, db.Connection);
                        int i = Convert.ToInt32(comm.ExecuteScalar());
                        if (i < 1)
                        {
                            MessageBox.Show("没有该内容");
                        return;
                        }
                        set.Tables["song_info"].Clear();
                        sql = "select song_id,song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info" + " inner join singer_info on song_info.singer_id = singer_info.singer_id " + "inner join song_type on song_info.songtype_id = song_type.songtype_id where song_name like N'%" + this.textBox1.Text + "%' ";
                        adapter = new SqlDataAdapter(sql, db.Connection);
                        adapter.Fill(set, "song_info");
                        this.dataGridView1.DataSource = set.Tables["song_info"];

                        db.CloseConnection();
                    }
                    else if (this.comboBox1.Text.Trim() == "按歌手查询" )
                    {
                    set.Tables["song_info"].Clear();
                    sql = "select * from [dbo].[singer_info] where  singer_name like N'%" + this.textBox1.Text + "%'";
                    SqlCommand comm = new SqlCommand(sql, db.Connection);
                    int i = Convert.ToInt32(comm.ExecuteScalar());
                    if (i < 1)
                    {
                        MessageBox.Show("没有该内容");
                        return;
                    }
                    
                        sql = "select song_id,song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info" + " inner join singer_info on song_info.singer_id = singer_info.singer_id " + "inner join song_type on song_info.songtype_id = song_type.songtype_id where singer_name like N'%" + this.textBox1.Text + "%' ";
                        adapter = new SqlDataAdapter(sql, db.Connection);
                        adapter.Fill(set, "song_info");
                        this.dataGridView1.DataSource = set.Tables["song_info"];

                        db.OpenConnection();
                    }
                    else if (this.comboBox1.Text.Trim() == "按歌名字数查询" )
                    {
                    set.Tables["song_info"].Clear();
                    


                    if (Regex.IsMatch(textBox1.Text,@"^[+-]?\d*[.]?$"))
                    {

                        int l = Convert.ToInt32(this.textBox1.Text);

                        sql = "select * from [dbo].[song_info] where  song_word_count=N'" + l + "'";
                        SqlCommand comm = new SqlCommand(sql, db.Connection);
                        int i = Convert.ToInt32(comm.ExecuteScalar());
                        if (i < 1)
                        {
                            MessageBox.Show("没有该内容");
                            return;
                        }
                        sql = "select song_id,song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info" + " inner join singer_info on song_info.singer_id = singer_info.singer_id " + "inner join song_type on song_info.songtype_id = song_type.songtype_id where song_word_count = N'" + l + "' ";
                        adapter = new SqlDataAdapter(sql, db.Connection);
                        adapter.Fill(set, "song_info");
                        this.dataGridView1.DataSource = set.Tables["song_info"];

                        db.OpenConnection();

                    }
                    else
                    {
                        MessageBox.Show("请输入数字：");
                        
                    }
                    
                    }
                    else if (this.comboBox1.Text.Trim() == "按拼音查询")
                    {

                    set.Tables["song_info"].Clear();
                    sql = "select * from [dbo].[song_info] where  song_ab like N'%" + this.textBox1.Text + "%'";
                    SqlCommand comm = new SqlCommand(sql, db.Connection);
                    int i = Convert.ToInt32(comm.ExecuteScalar());
                    if (i < 1)
                    {
                        MessageBox.Show("没有该内容");
                        return;
                    }
                    
                        sql = "select song_id,song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info" + " inner join singer_info on song_info.singer_id = singer_info.singer_id " + "inner join song_type on song_info.songtype_id = song_type.songtype_id where song_ab like N'%" + this.textBox1.Text + "%' ";
                        adapter = new SqlDataAdapter(sql, db.Connection);
                        adapter.Fill(set, "song_info");
                        this.dataGridView1.DataSource = set.Tables["song_info"];

                        db.OpenConnection();
                    }
            }
            else
            {
                MessageBox.Show("查询条件不能为空");
            }
        }

        //删除数据 
        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DialogResult result = MessageBox.Show("确定删除吗！","提示框",MessageBoxButtons.YesNo,MessageBoxIcon.Warning);
            if (result==DialogResult.Yes)
            {
                db.OpenConnection();
                string sql = "delete from song_info where song_name=N'" + this.dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "'";

                SqlCommand comm = new SqlCommand(sql, db.Connection);
                int iasd = comm.ExecuteNonQuery();
                if (iasd > 0)

                {

                    MessageBox.Show("删除成功");

                    set.Tables["song_info"].Clear();

                    db.OpenConnection();

                    string ss = "select song_id,song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info inner join singer_info on song_info.singer_id = singer_info.singer_id inner join song_type on song_info.songtype_id = song_type.songtype_id ";

                    SqlDataAdapter adapter = new SqlDataAdapter(ss, db.Connection);

                    adapter.Fill(set, "song_info");

                    this.dataGridView1.DataSource = set.Tables["song_info"];


                    db.CloseConnection();

                }else
                {
                    MessageBox.Show("取消删除");
                }
                
            }

        } 

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
        }
        private void 修改ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            xiugai_gequ xg = new xiugai_gequ();

            xg.geming = dataGridView1.SelectedRows[0].Cells["song_name"].Value.ToString();
            xg.pinsuo = dataGridView1.SelectedRows[0].Cells["song_ab"].Value.ToString();
            xg.gelei = dataGridView1.SelectedRows[0].Cells["songtype_name"].Value.ToString();
            xg.geshou = dataGridView1.SelectedRows[0].Cells["singer_name"].Value.ToString();
            xg.geci = dataGridView1.SelectedRows[0].Cells["song_play_count"].Value.ToString();
            xg.dizhi  = dataGridView1.SelectedRows[0].Cells["song_url"].Value.ToString();
            xg.ShowDialog();
        }
        
    }
    }
